/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.jdbc.dao;

import com.thienloc.omap.jdbc.entity.RequestQueue;
import com.thienloc.omap.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author GiangLT
 */
public class RequestQueueDao extends BaseDao {

    public List<RequestQueue> findByStatusNullServiceOnly(List<String> statuses, int MAX_RESULT, boolean desc) throws Exception {
        List<RequestQueue> result = new ArrayList<RequestQueue>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {

            StringBuilder query = new StringBuilder("SELECT * FROM request_queue r WHERE r.status IN ('");
            for (String status : statuses) {
                query.append(status);
                query.append("', '");
            }
            query.delete(query.lastIndexOf(", '"), query.length());
            query.append(")").append(" AND (r.service_name IS NULL OR r.service_name = '')").append(" AND rownum < ").append(MAX_RESULT).append(" ORDER BY r.received_time");
            if (desc) {
                query.append(" DESC");
            }

            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(query.toString());

            rs = ps.executeQuery();
            while (rs.next()) {
                RequestQueue requestQueue = new RequestQueue();
                requestQueue.setRequestId(rs.getLong(1));
                requestQueue.setRequestDetail(rs.getString(2));
                requestQueue.setParam(rs.getString(3));
                requestQueue.setReceivedTime(rs.getDate(4));
                requestQueue.setMsisdn(rs.getString(5));
                requestQueue.setCpNumber(rs.getString(6));
                requestQueue.setServiceName(rs.getString(7));
                requestQueue.setStatus(rs.getString(8));
                requestQueue.setLastCheckTime(rs.getDate(9));
                requestQueue.setCallBack(rs.getString(10));
                requestQueue.setDeliverProvider(rs.getString(11));
                requestQueue.setResponseContent(rs.getString(12));
                requestQueue.setSelected(rs.getInt(13));
                requestQueue.setOldContent(rs.getString(14));

                result.add(requestQueue);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public List<RequestQueue> findByStatus(List<String> statuses, int MAX_RESULT, boolean desc) throws Exception {
        List<RequestQueue> result = new ArrayList<RequestQueue>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {

            StringBuilder query = new StringBuilder("SELECT * FROM request_queue r WHERE r.status IN ('");
            for (String status : statuses) {
                query.append(status);
                query.append("', '");
            }
            query.delete(query.lastIndexOf(", '"), query.length());
            query.append(")").append(" AND rownum < ").append(MAX_RESULT).append(" ORDER BY r.received_time");
            if (desc) {
                query.append(" DESC");
            }

            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(query.toString());

            rs = ps.executeQuery();
            while (rs.next()) {
                RequestQueue requestQueue = new RequestQueue();
                requestQueue.setRequestId(rs.getLong(1));
                requestQueue.setRequestDetail(rs.getString(2));
                requestQueue.setParam(rs.getString(3));
                requestQueue.setReceivedTime(rs.getDate(4));
                requestQueue.setMsisdn(rs.getString(5));
                requestQueue.setCpNumber(rs.getString(6));
                requestQueue.setServiceName(rs.getString(7));
                requestQueue.setStatus(rs.getString(8));
                requestQueue.setLastCheckTime(rs.getDate(9));
                requestQueue.setCallBack(rs.getString(10));
                requestQueue.setDeliverProvider(rs.getString(11));
                requestQueue.setResponseContent(rs.getString(12));
                requestQueue.setSelected(rs.getInt(13));
                requestQueue.setOldContent(rs.getString(14));

                result.add(requestQueue);
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

//    public void create(RequestQueue selected) {
//        throw new UnsupportedOperationException("Not yet implemented");
//    }
    public void edit(RequestQueue requestQueue) throws Exception {
        if (requestQueue.getRequestId() == null) {
            throw new Exception("This request does not have an id");
        }
        String sql = "update REQUEST_QUEUE set "
                + " SELECTED = ?, "
                + " OLD_CONTENT = ?, "
                + " REQUEST_DETAIL = ?, "
                + " PARAM = ?, "
                + " RECEIVED_TIME = ?, "
                + " MSISDN = ?,"
                + " CP_NUMBER = ?,"
                + " SERVICE_NAME = ?,"
                + " STATUS = ?, "
                + " LAST_CHECK_TIME = ?,  "
                + " CALL_BACK = ?, "
                + " DELIVER_PROVIDER = ?, "
                + " RESPONSE_CONTENT = ? "
                + " where REQUEST_ID = ? ";
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(sql);
            int i = 1;
            ps.setInt(i++, requestQueue.getSelected());
            ps.setString(i++, requestQueue.getOldContent());
            ps.setString(i++, requestQueue.getRequestDetail());
            ps.setString(i++, requestQueue.getParam());
            ps.setDate(i++, new java.sql.Date(requestQueue.getReceivedTime().getTime()));
            ps.setString(i++, requestQueue.getMsisdn());
            ps.setString(i++, requestQueue.getCpNumber());
            ps.setString(i++, requestQueue.getServiceName());
            ps.setString(i++, requestQueue.getStatus());
            ps.setDate(i++, new java.sql.Date(requestQueue.getLastCheckTime().getTime()));
            ps.setString(i++, requestQueue.getCallBack());
            ps.setString(i++, requestQueue.getDeliverProvider());
            ps.setString(i++, requestQueue.getResponseContent());
            ps.setLong(i++, requestQueue.getRequestId());

            ps.executeUpdate();
            conn.commit();
        } catch (Exception ex) {
            conn.rollback();
            throw ex;
        } finally {
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

    }

    public void batchUpdate(List<RequestQueue> items) throws Exception {
        String sql = "update REQUEST_QUEUE set "
                + " SELECTED = ?, "
                + " OLD_CONTENT = ?, "
                + " REQUEST_DETAIL = ?, "
                + " PARAM = ?, "
                + " RECEIVED_TIME = ?, "
                + " MSISDN = ?,"
                + " CP_NUMBER = ?,"
                + " SERVICE_NAME = ?,"
                + " STATUS = ?, "
                + " LAST_CHECK_TIME = ?,  "
                + " CALL_BACK = ?, "
                + " DELIVER_PROVIDER = ?, "
                + " RESPONSE_CONTENT = ? "
                + " where REQUEST_ID = ? ";
        PreparedStatement ps = null;
        Connection conn = null;

        try {
            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(sql);

            for (RequestQueue requestQueue : items) {
                if (requestQueue.getRequestId() != null) {
                    int i = 1;
                    ps.setInt(i++, requestQueue.getSelected());
                    ps.setString(i++, requestQueue.getOldContent());
                    ps.setString(i++, requestQueue.getRequestDetail());
                    ps.setString(i++, requestQueue.getParam());
                    ps.setDate(i++, new java.sql.Date(requestQueue.getReceivedTime().getTime()));
                    ps.setString(i++, requestQueue.getMsisdn());
                    ps.setString(i++, requestQueue.getCpNumber());
                    ps.setString(i++, requestQueue.getServiceName());
                    ps.setString(i++, requestQueue.getStatus());
                    ps.setDate(i++, new java.sql.Date(requestQueue.getLastCheckTime().getTime()));
                    ps.setString(i++, requestQueue.getCallBack());
                    ps.setString(i++, requestQueue.getDeliverProvider());
                    ps.setString(i++, requestQueue.getResponseContent());
                    ps.setLong(i++, requestQueue.getRequestId());
                    ps.addBatch();
                }

            }


            ps.executeBatch();
            conn.commit();
        } catch (Exception ex) {
            conn.rollback();
            throw ex;
        } finally {
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }
    }

    public void remove(RequestQueue item) throws Exception {
        if (item.getRequestId() == null) {
            throw new Exception("This request does not have an id");
        }

        String sql = "delete from REQUEST_QUEUE where REQUEST_ID = ?";
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(sql);
            ps.setLong(1, item.getRequestId());

            ps.executeUpdate();
            conn.commit();
        } catch (Exception ex) {
            conn.rollback();
            throw ex;
        } finally {
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }
    }

    public RequestQueue find(Long id) throws Exception {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            String query = "select * from REQUEST_QUEUE where REQUEST_ID = ?";

            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(query);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                RequestQueue result = new RequestQueue();
                result.setRequestId(rs.getLong(1));
                result.setRequestDetail(rs.getString(2));
                result.setParam(rs.getString(3));
                result.setReceivedTime(rs.getDate(4));
                result.setMsisdn(rs.getString(5));
                result.setCpNumber(rs.getString(6));
                result.setServiceName(rs.getString(7));
                result.setStatus(rs.getString(8));
                result.setLastCheckTime(rs.getDate(9));
                result.setCallBack(rs.getString(10));
                result.setDeliverProvider(rs.getString(11));
                result.setResponseContent(rs.getString(12));
                result.setSelected(rs.getInt(13));
                result.setOldContent(rs.getString(14));
                return result;
            }
            return null;
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

    }
}
